import java.io.*;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DatabaseInitializer {
    private static final String DB_URL = "jdbc:mysql://localhost:";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "17842402115";
    private static final String DB_PORT = "3306";
    private static final String SQL_URL_SUFFIX = "?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false";

    public static void main(String[] args) {
        try {
            initializeDatabases();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化数据库：读取 SQL 文件、创建数据库、执行 SQL 语句。
     */
    public static void initializeDatabases() throws Exception {
        String currentDir = System.getProperty("user.dir");
        List<File> sqlFiles = getSqlFilesFromDirectory(currentDir);

        // 加载 MySQL JDBC 驱动
        loadJdbcDriver();

        // 建立与数据库的连接
        try (Connection connection = getDatabaseConnection(DB_URL + DB_PORT + SQL_URL_SUFFIX)) {
            Statement statement = connection.createStatement();

            for (File sqlFile : sqlFiles) {
                String dbName = sqlFile.getName().replace(".sql", "");
                String dbUrl = DB_URL + DB_PORT + "/" + dbName + SQL_URL_SUFFIX;

                // 打印正在创建的数据库名称
                System.out.println("正在创建数据库: " + dbName);

                // 检查并创建数据库
                handleDatabaseCreation(statement, dbName);

                // 执行 SQL 文件
                executeSQLFile(sqlFile, dbUrl);

                // 执行完毕后打印日志
                System.out.println("SQL 文件 " + sqlFile.getName() + " 执行完毕！");
            }

            //delayAndDeleteCurrentFile();

        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("Error while connecting to MySQL and initializing databases.", e);
        }
    }

    /**
     * 获取项目目录和 DB 子目录中的所有 .sql 文件
     */
    private static List<File> getSqlFilesFromDirectory(String currentDir) {
        List<File> sqlFiles = new ArrayList<>();
        File sqlDir = new File(currentDir);
        if (sqlDir.exists() && sqlDir.isDirectory()) {
            for (File sqlFile : Objects.requireNonNull(sqlDir.listFiles((dir, name) -> name.endsWith(".sql")))) {
                sqlFiles.add(sqlFile);
            }
        }

        // 读取 DB 文件夹中的 SQL 文件
        File dbDir = new File(currentDir, "DB");
        if (dbDir.exists() && dbDir.isDirectory()) {
            for (File sqlFile : Objects.requireNonNull(dbDir.listFiles((dir, name) -> name.endsWith(".sql")))) {
                sqlFiles.add(sqlFile);
            }
        }
        return sqlFiles;
    }

    /**
     * 加载 MySQL JDBC 驱动，优化以支持多个 MySQL 版本
     */
    private static void loadJdbcDriver() throws ClassNotFoundException {
        try {
            // 首先尝试加载 MySQL 8+ 的驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e1) {
            try {
                // 如果失败，尝试加载 MySQL 5.x 的驱动
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e2) {
                System.err.println("未找到 MySQL 驱动程序");
                throw e2;
            }
        }
    }

    /**
     * 获取数据库连接，增加重试机制和错误处理
     */
    private static Connection getDatabaseConnection(String dbUrl) throws SQLException {
        int maxRetries = 3;
        int retryCount = 0;
        SQLException lastException = null;

        while (retryCount < maxRetries) {
            try {
                // 尝试使用新的认证方式连接
                return DriverManager.getConnection(dbUrl, DB_USER, DB_PASSWORD);
            } catch (SQLException e) {
                lastException = e;
                retryCount++;

                if (e.getMessage().contains("caching_sha2_password")) {
                    System.out.println("正在尝试使用替代认证方式连接... 尝试 " + retryCount + "/" + maxRetries);
                    // 如果是认证问题，尝试使用旧的认证方式
                    try {
                        String altUrl = dbUrl + "&useOldAuthenticationMethod=true";
                        return DriverManager.getConnection(altUrl, DB_USER, DB_PASSWORD);
                    } catch (SQLException ignored) {
                        // 如果旧认证方式也失败，继续重试
                    }
                }

                if (retryCount < maxRetries) {
                    try {
                        Thread.sleep(1000); // 等待1秒后重试
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        throw new SQLException("连接被中断", ie);
                    }
                }
            }
        }

        throw new SQLException("在 " + maxRetries + " 次尝试后仍无法连接到数据库", lastException);
    }
    /**
     * 处理数据库创建（如果存在则删除，重新创建）
     */
    private static void handleDatabaseCreation(Statement statement, String dbName) throws SQLException {
        String dropDatabaseSQL = "DROP DATABASE IF EXISTS `" + dbName + "`;";
        statement.execute(dropDatabaseSQL);

        String createDbSQL = String.format(
                "CREATE DATABASE IF NOT EXISTS `%s` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;", dbName);
        statement.execute(createDbSQL);

        // 打印创建数据库完成的日志
        System.out.println("数据库 " + dbName + " 创建完成！");
    }

    /**
     * 执行 SQL 文件中的所有语句，自动处理 BOM 问题
     */
    private static void executeSQLFile(File sqlFile, String dbUrl) throws IOException, SQLException {
        String currentDate = getCurrentDateTime();
        StringBuilder sqlBuilder = new StringBuilder();

        try (Connection dbConnection = getDatabaseConnection(dbUrl);
             Statement dbStatement = dbConnection.createStatement();
             BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(sqlFile), StandardCharsets.UTF_8))) {

            String line;
            boolean inMultiLineComment = false;

            // 检查并跳过 BOM 标记
            line = reader.readLine();
            if (line != null && line.startsWith("\uFEFF")) {
                line = line.substring(1); // 移除 BOM
            }

            while (line != null) {
                if (shouldSkipLine(line, inMultiLineComment)) {
                    // 判断是否进入或退出多行注释
                    if (line.contains("/*")) inMultiLineComment = true;
                    if (line.contains("*/")) inMultiLineComment = false;
                } else {
                    // 替换日期
                    line = replaceDateWithCurrent(line, currentDate);
                    sqlBuilder.append(line);

                    // 如果遇到分号，执行当前语句
                    if (line.endsWith(";")) {
                        try {
                            dbStatement.execute(sqlBuilder.toString());
                        } catch (SQLException e) {
                            logSqlExecutionError(sqlBuilder, e);
                            throw e;
                        }
                        sqlBuilder.setLength(0); // 清空缓冲区
                    }
                }

                // 读取下一行
                line = reader.readLine();
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
            throw new SQLException("执行 SQL 脚本时出错，文件：" + sqlFile.getName(), e);
        }
    }

    /**
     * 判断是否跳过当前行
     */
    private static boolean shouldSkipLine(String line, boolean inMultiLineComment) {
        return line.isEmpty() || line.startsWith("--") || line.contains("Navicat MySQL Data Transfer") || line.startsWith("#") ||
                line.contains("Source Server") || line.contains("Target Server") || line.startsWith("/*") ||
                inMultiLineComment;
    }

    /**
     * 记录 SQL 执行错误信息
     */
    private static void logSqlExecutionError(StringBuilder sqlBuilder, SQLException e) {
        System.err.println("SQL 执行错误：");
        System.err.println("错误的 SQL：" + sqlBuilder);
        System.err.println("错误信息：" + e.getMessage());
    }

    /**
     * 获取当前日期时间，格式：yyyy-MM-dd HH:mm:ss
     */
    private static String getCurrentDateTime() {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        return LocalDateTime.now().format(formatter);
    }

    /**
     * 替换 SQL 语句中的日期字符串
     */
    private static String replaceDateWithCurrent(String sql, String currentDate) {
        String datePattern = "'(\\d{4}-\\d{2}-\\d{2})( \\d{2}:\\d{2}(?::\\d{2})?)?'";
        Pattern pattern = Pattern.compile(datePattern);
        Matcher matcher = pattern.matcher(sql);

        StringBuilder result = new StringBuilder();
        int lastEnd = 0;

        while (matcher.find()) {
            String datePart = matcher.group(1);  // yyyy-MM-dd 部分
            String timePart = matcher.group(2);  // 空格和 HH:mm:ss 部分（如果有）
            String newDate = "'" + currentDate.substring(0, 10); // 替换为当前日期部分
            if (timePart != null) newDate += timePart; // 如果有时间部分，添加上
            newDate += "'";  // 确保替换后的日期有引号
            result.append(sql, lastEnd, matcher.start());
            result.append(newDate);
            lastEnd = matcher.end();
        }

        result.append(sql.substring(lastEnd));
        return result.toString();
    }

    /**
     * 延迟 3 秒并删除当前文件
     */
    private static void delayAndDeleteCurrentFile() throws InterruptedException, IOException {
        // 延迟 3 秒
        Thread.sleep(3000);
        try {
            // 获取当前项目的根目录（即工作目录）
            String projectDir = System.getProperty("user.dir");
            // 拼接出目标文件路径
            Path fileToDelete = Paths.get(projectDir, "src", "DatabaseInitializer.java");
            // 删除文件
            if (Files.exists(fileToDelete)) {
                Files.delete(fileToDelete);
                System.out.println("服务已经结束");
            } else {
                System.err.println("Java 文件不存在，无法删除: " + fileToDelete);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
